﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CatouseDAO.NET
{
    /// <summary>
    /// SQL数据库访问类
    /// </summary>
    public class SQLDataAccess
    {
        private String connectionString;

        /// <summary>
        /// 获取和设置数据库连接字符串
        /// </summary>
        public String ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        /// <summary>
        /// 使用数据库连接字符串构造该对象
        /// </summary>
        /// <param name="conStr">数据库连接字符串</param>
        public SQLDataAccess(String conStr)
        {
            ConnectionString = conStr;
        }

        /// <summary>
        /// 自动获取数据库连接字符串
        /// </summary>
        public SQLDataAccess()
        {
            ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[0];
        }

        /// <summary>
        /// 执行不带返回结果集的SQL语句,参数是Command对象,返回受影响的行数
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <returns>受影响的行数</returns>
        public int SqlExecuteNonQuery(SqlCommand cmd)
        {
            //创建connection和Command对象
            SqlConnection conn = new SqlConnection(ConnectionString);
            cmd.Connection = conn;

            try
            {
                //打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                //返回执行影响的条数
                return cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 执行不带返回结果集的SQL语句,参数是string类型
        /// </summary>
        /// <param name="cmdStr">SQL字符串</param>
        /// <returns>受影响的行数</returns>
        public int SqlExecuteNonQuery(string cmdStr)
        {
            SqlCommand cmd = new SqlCommand(cmdStr);
            return SqlExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 以DataReader类型返回查询结果,参数是Command类型
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <returns>SQLDataReader对象</returns>
        public SqlDataReader SqlExecuteReader(SqlCommand cmd)
        {
            //创建connection和Command对象
            SqlConnection conn = new SqlConnection(ConnectionString);
            cmd.Connection = conn;

            try
            {
                //打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                //返回执行影响的条数
                return cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 以DataReader类型返回查询结果,参数是Command类型
        /// </summary>
        /// <param name="cmd">SQL字符串</param>
        /// <returns>SQLDataReader对象</returns>
        public SqlDataReader SqlExecuteReader(string cmdStr)
        {
            SqlCommand cmd = new SqlCommand(cmdStr);
            return SqlExecuteReader(cmd);
        }

        /// <summary>
        /// 返回结果集中第一行的第一列数据对象,参数是Command类型
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <returns>object类型的数据封装对象</returns>
        public object SqlExecuteScalar(SqlCommand cmd)
        {
            //创建connection和Command对象
            SqlConnection conn = new SqlConnection(ConnectionString);
            cmd.Connection = conn;

            try
            {
                //打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                //返回首行首列对象
                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 返回结果集中第一行的第一列数据对象,参数是Command类型
        /// </summary>
        /// <param name="cmd">SQL字符串</param>
        /// <returns>object类型的数据封装对象</returns>
        public object SqlExecuteScalar(string cmdStr)
        {
            SqlCommand cmd = new SqlCommand(cmdStr);
            return SqlExecuteScalar(cmd);
        }

        /// <summary>
        /// 根据Command填充DataSet,参数是Command类型
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetDataSet(SqlCommand cmd, string strTableName)
        {
            //创建connection和Command对象
            SqlConnection conn = new SqlConnection(ConnectionString);
            cmd.Connection = conn;

            try
            {
                //打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                //填充并返回DataSet对象
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds, strTableName);
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 根据Command填充DataSet,参数是Command类型
        /// </summary>
        /// <param name="cmd">SQL字符串</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetDataSet(string cmdStr, string strTableName)
        {
            SqlCommand cmd = new SqlCommand(cmdStr);
            return GetDataSet(cmd, strTableName);
        }

        /// <summary>
        /// 将Dataset更新到数据库
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName">表名称</param>
        /// <param name="cmdStr">SQL字符串</param>
        public void FillDataSetToDatabase(string cmdStr, DataSet dataset, string tableName)
        {
            //创建connection和Command对象
            SqlCommand cmd = new SqlCommand(cmdStr);
            SqlConnection conn = new SqlConnection(ConnectionString);
            cmd.Connection = conn;
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                //填充并返回DataSet对象
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(dataset,tableName);

                SqlCommandBuilder scb = new SqlCommandBuilder(adp);
                adp.Update(dataset, tableName);
                //return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 将Dataset更新到数据库
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableIndex">表索引号</param>
        /// <param name="cmdStr">SQL字符串</param>
        public void FillDataSetToDatabase(string cmdStr, DataSet dataset, int tableIndex)
        {
            //创建connection和Command对象
            SqlCommand cmd = new SqlCommand(cmdStr);
            SqlConnection conn = new SqlConnection(ConnectionString);
            cmd.Connection = conn;
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                //填充并返回DataSet对象
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(dataset.Tables[tableIndex]);

                SqlCommandBuilder scb = new SqlCommandBuilder(adp);
                adp.Update(dataset.Tables[tableIndex]);
                //return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}
